<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
$bar="Numberday of all staff";
        echo '<table class="tablebg" cellspacing="1" align="right" border="1" width="900px">';
        echo '<tr><td colspan=12 align=right class=bar>'.$bar.'</td></tr>';
        echo '<tr><td colspan=12 align=right class=bar>Totals of results: '.$total_rows.'</td></tr>';
        echo '<tr><td colspan=12 align=right class=pagination>'.$pagination.'</td></tr>';
        echo '
              <th>fullname</th>
              <th>OT_Weekday</th>
              <th>OT_Saturday</th>
              <th>OT_Sunday</th>
              <th>overnight</th>
              <th>workedday</th>
              <th>workingday</th>
              <th>timesat</th>
              <th>timesun</th>
              ';
        foreach($query->result() as $row){
            //$images='<img src="'.base_url().'application/templates/images/'.$row->staff_avarta.'" width=100 height=70>';
            //$details = anchor("user/loadUpdateForm/$row->Staff_ID",$images);
            echo "<tr>
                        ";
            $sql="select * from ewt_users where id=$row->userid";
            $query1=$this->db->query($sql);
            foreach ($query1->result() as $row1)
            {
            echo "<td class=\"row1\">$row1->fullname</td>";      
            }
            
            
                  
            
            $sql2="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime AND (
weekday = 'Monday'
OR weekday = 'Tuesday'
OR weekday = 'Wednesday'
OR weekday = 'Thursday'
OR weekday = 'Firday'
)
AND substr( overtime, 4, 2 ) !=0";
            $query2=$this->db->query($sql2);
            $a=0;$b=0;$c=0;$d=0;$e=0;$f=0;$g=0;
            $i=0;$k=0;
            $gio=0;
            $phut=0;
            $gio1=0;
            $phut1=0;
            foreach($query2->result() as $row2)
            {
                $phut=$phut+substr($row2->overtime,3,2);
                $gio=$gio+substr($row2->overtime,0,2);
                $phut1=$phut%60;
                $gio1=(int)($phut/60)+$gio;
            }
            $i=$gio1.':'.$phut1;
            
            echo "<td class=\"row1\">$i</td>";
            
            
            $sql8="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime AND ( weekday = 'Saturday') AND substr( overtime, 4, 2 ) !=0";
            $query8=$this->db->query($sql8);
            
            $gio2=0;
            $phut2=0;
            $gio3=0;
            $phut3=0;
            foreach($query8->result() as $row8)
            {
                $phut2=$phut2+substr($row8->overtime,3,2);
                $gio2=$gio2+substr($row8->overtime,0,2);
                $phut3=$phut2%60;
                $gio3=(int)($phut2/60)+$gio2;
            }
            $i1=$gio3.':'.$phut3;
            
            echo "<td class=\"row1\">$i1</td>";
            
            $sql9="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime AND ( weekday = 'Sunday') AND substr( overtime, 4, 2 ) !=0";
            $query9=$this->db->query($sql9);
            
            $gio4=0;
            $phut4=0;
            $gio5=0;
            $phut5=0;
            foreach($query9->result() as $row9)
            {
                $phut4=$phut4+substr($row9->overtime,3,2);
                $gio4=$gio4+substr($row9->overtime,0,2);
                $phut5=$phut4%60;
                $gio5=(int)($phut4/60)+$gio4;
            }
            $i2=$gio5.':'.$phut5;
            
            echo "<td class=\"row1\">$i2</td>";
            
            $sql7="select count(*) as numberdayovernight from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and ((substr(starttime,12,2)>='21' and substr(endtime,12,2)<'24') or (substr(starttime,12,2)>'00' and substr(starttime,12,2)<'05'))";
            $query7=$this->db->query($sql7);
            foreach ($query7->result() as $row7)
            {
            echo "<td class=\"row1\">$row7->numberdayovernight</td>";
                
            }
            $sql3="select count(*) as numbersunday from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and weekday='Sunday'";
            $query3=$this->db->query($sql3);
            foreach ($query3->result() as $row3)
            {
                //echo "<td class=\"row1\">$row3->numbersunday</td>";
            }
             $sql4="select count(*) as numbersaturday from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and weekday='Saturday'";
            $query4=$this->db->query($sql4);
            $hoursat=0;
            $minutesat=0;
            $hoursat1=0;
            $minutesat1=0;
            foreach ($query4->result() as $row4)
            {
                //echo "<td class=\"row1\">$row4->numbersaturday</td>"; 
            }
            //echo "<td class=\"row1\">$row4->numbersaturday</td>";
             $sql5="select count(*) as numbersatall from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate'  and weekday='Saturday'";
            $query5=$this->db->query($sql5);
            foreach ($query5->result() as $row5)
            {
                //echo "<td class=\"row1\">$row5->numbersatall</td>";
            }
            $sql6="select count(*) as numbersundaya from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and weekday='Sunday'";
            $query6=$this->db->query($sql6);
            foreach ($query6->result() as $row6)
            {
                //echo "<td class=\"row1\">$row3->numbersunday</td>";
            }
            $workedday=$row->numberday-$row3->numbersunday+$row5->numbersatall-$row4->numbersaturday;
            echo "<td class=\"row1\">$workedday</td>";
            $sql30="select count(distinct substr(starttime,1,10)) as totalday from ewt_works where starttime between '$fromdate' and '$todate'";
            $query30=$this->db->query($sql30);
            foreach ($query30->result() as $row30)
            {
                
            }
            $sql31="select count(distinct substr(starttime,1,10)) as totalsunday from ewt_works where weekday='Sunday' and starttime between '$fromdate' and '$todate'";
            $query31=$this->db->query($sql31);
            foreach ($query31->result() as $row31)
            {
                
            }
            $workingday=$row30->totalday-$row31->totalsunday;
            echo "<td class=\"row1\">$workingday</td>";
            $sql10="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and weekday='Saturday'";
            $query10=$this->db->query($sql10);
            $hoursatturday=0;
            $minutesaturday=0;
            $hoursatturday1=0;
            $minutesaturday1=0;
            foreach($query10->result() as $row10)
            {
                //echo substr($row10->starttime,14,2);
                if(substr($row10->starttime,14,2)>substr($row10->endtime,14,2))
                {
                    $phutthu7=substr($row10->endtime,14,2)+60-substr($row10->starttime,14,2);
                    if((substr($row10->endtime,11,12)<=13||substr($row10->starttime,11,12)>=13)&&substr($row10->starttime,11,12)!=0)
                    $giothu7=substr($row10->endtime,11,2)-substr($row10->starttime,11,2)-1;
                    else {
                    $giothu7=substr($row10->endtime,11,2)-substr($row10->starttime,11,2)-2;
                    }
                }
                else
                {
                    $phutthu7=substr($row10->endtime,14,2)-substr($row10->starttime,14,2);
                    if((substr($row10->endtime,11,12)<=13||substr($row10->starttime,11,12)>=13)&&substr($row10->starttime,11,12)!=0)
                    $giothu7=substr($row10->endtime,11,2)-substr($row10->starttime,11,2);
                    else
                    {
                    $giothu7=substr($row10->endtime,11,2)-substr($row10->starttime,11,2)-1;
                    }
                }
                
                $minutesaturday=$minutesaturday+$phutthu7;
                $hoursatturday=$hoursatturday+$giothu7;
                $minutesaturday1=$minutesaturday%60;
                $hoursatturday1=(int)($minutesaturday/60)+$hoursatturday;
            }
            if($minutesaturday1<$phut3)
            {
                $minutesatday=$minutesaturday1+60-$phut3;
                $hoursatday=$hoursatturday1-1-$gio3;
            }
            else
            {
                $minutesatday=$minutesaturday1-$phut3;
                $hoursatday=$hoursatturday1-$gio3;
            }
            echo "<td class=\"row1\">$hoursatday:$minutesatday</td>";
            $sql11="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and weekday='Sunday'";
            $query11=$this->db->query($sql11);
            $hoursunday=0;
            $minutesunday=0;
            $hoursunday1=0;
            $minutesunday1=0;
            foreach($query11->result() as $row11)
            {
                //echo substr($row11->starttime,14,2);
                if(substr($row11->starttime,14,2)>substr($row11->endtime,14,2))
                {
                    $phutcn=substr($row11->endtime,14,2)+60-substr($row11->starttime,14,2);
                    if((substr($row11->endtime,11,12)<=13||substr($row11->starttime,11,12)>=13)&&substr($row11->starttime,11,12)!=0)
                    $giocn=substr($row11->endtime,11,2)-substr($row11->starttime,11,2)-1;
                    else
                        $giocn=substr($row11->endtime,11,2)-substr($row11->starttime,11,2)-2;
                }
                else
                {
                    $phutcn=substr($row11->endtime,14,2)-substr($row11->starttime,14,2);
                    if((substr($row11->endtime,11,12)<=13||substr($row11->starttime,11,12)>=13)&&substr($row11->starttime,11,12)!=0)
                    $giocn=substr($row11->endtime,11,2)-substr($row11->starttime,11,2);
                    else
                        $giocn=substr($row11->endtime,11,2)-substr($row11->starttime,11,2)-1;
                }
                
                $minutesunday=$minutesunday+$phutcn;
                $hoursunday=$hoursatturday+$giocn;
                $minutesunday1=$minutesunday%60;
                $hoursunday1=(int)($minutesunday/60)+$hoursunday;
            }
            if($minutesunday1<$phut5)
            {
                $minutesuday=$minutesunday1+60-$phut5;
                $hoursuday=$hoursunday1-1-$gio5;
            }
            else
            {
                $minutesuday=$minutesunday1-$phut5;
                $hoursuday=$hoursunday1-$gio5;
            }
            echo "<td class=\"row1\">$hoursuday:$minutesuday</td>";
            
            /*$titleUpdate = '<img src ="'.base_url().'templates/images/icons/edit.gif" title="Click here to modify"/>';
            $titleDelete = '<img src ="'.base_url().'templates/images/icons/delete.png" title="Click here to delete"/>';
            //$titleActive = '<img src ="'.base_url().'application/templates/images/icons/active.jpg" title="Click here to active"/>';
            $titleView = '<img src ="'.base_url().'templates/images/icons/view.gif" height=25 title="Click here to view "/>';
            $titleInsertFamily = '<img src ="'.base_url().'templates/images/icons/insert_family.jpeg" width=40 height=25 title="Click here to insert family"/>';
            $update = anchor("staff/loadStaffUpdateForm/$row->Staff_ID",$titleUpdate);
            $delete = anchor("staff/deleteStaff/$row->Staff_ID",$titleDelete);
            //$active = anchor("user/loadConfirmForm/$row->Staff_ID",$titleActive);
            $view_staff=anchor("staff/viewInformationStaff/$row->Staff_ID",$titleView);
            $insert_family=anchor("staff/loadListFamily/$row->Staff_ID",$titleInsertFamily);
            echo "<td class=\"row1\" align=\"center\">$update $delete $view_staff $insert_family</td></tr>";*/
            }
            /*$titleInsert = '<img src ="'.base_url().'templates/images/icons/addstaff.jpeg" width=60 height=40 title="Click here to insert staff"/>';
            $insert = anchor("staff/loadInterviewerPass",$titleInsert);
            echo '<tr><td colspan=5 align=center>'.$insert.'</td></tr>';
            echo '<tr><td colspan=5 align=right class=pagination>'.$pagination.'</td></tr>';*/
            
        echo '</table>';
?>
